﻿using System;
using System.Data;
using System.Data.SqlClient;
using Dtl;
using System.Collections.Generic;
using System.Collections;

namespace Dal
{
    public class DistribuitorEmailDAO
    {
        /*LISTADO DE EMAILS POR DISTRIBUIDOR*/
        public static DataSet getEmailsBydistributors(int idDistributor) 
        {

            string cnx = Credentials.getCredentias("DMSTools");
            if (cnx == null) return null;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(cnx);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dms_GetEmailByDistributors";
            cmd.Parameters.AddWithValue("@idDistributor", idDistributor);
            cmd.CommandTimeout = 0;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("dtEmailList");
            try
            {
                cmd.Connection.Open();
                da.Fill(dt);
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                Credentials.writeLogFile("getEmailsBydistributors: " + ex.Message);
                return null;
            }
            DataSet ds = new DataSet("dsEmailList");
            ds.Tables.Add(dt);
            return ds;
        }


        public static DataSet getPhaseBydistributors(int idDistributor)
        {

            string cnx = Credentials.getCredentias("DMSTools");
            if (cnx == null) return null;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(cnx);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dms_GetPhaseByDistribuitor";
            cmd.Parameters.AddWithValue("@idDistributor", idDistributor);
            cmd.CommandTimeout = 0;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("dtPhaseBydistributors");
            try
            {
                cmd.Connection.Open();
                da.Fill(dt);
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                Credentials.writeLogFile("getPhaseBydistributors: " + ex.Message);
                return null;
            }
            DataSet ds = new DataSet("dsPhaseBydistributors");
            ds.Tables.Add(dt);
            return ds;
        }

        public static DataSet getPhases()
        {

            string cnx = Credentials.getCredentias("DMSTools");
            if (cnx == null) return null;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(cnx);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "GetPhases";             
            cmd.CommandTimeout = 0;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("dtPhases");
            try
            {
                cmd.Connection.Open();
                da.Fill(dt);
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                Credentials.writeLogFile("getPhases: " + ex.Message);
                return null;
            }
            DataSet ds = new DataSet("dsPhases");
            ds.Tables.Add(dt);
            return ds;
        }



        public static int setDistribuitorPhaseAndEmail(int idDistribuitor, bool sendAlert, List<Dtl.DistribuitorEmail> listEmail, List<Dtl.DistribuitorPhase> lisDistPhase)
        {
            int insert = 0;

            string connection = Credentials.getCredentias("DMSTools");
            if (connection == null)
            {
                return 0;
            }
            
            SqlTransaction tr = null;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);

            try
            {
                cmd.Connection.Open();
                cmd.Connection.CreateCommand();
                tr = cmd.Connection.BeginTransaction("Transaction");
                cmd.Transaction = tr;

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                

                //ACTUALIZA EL ESTADO DE sendAlert en la tabla DMS_distribuidor
                cmd.CommandText = "dbo.dms_SetDistributor_Change";
                cmd.Parameters.AddWithValue("@IdDistributor", idDistribuitor);
                cmd.Parameters.AddWithValue("@SendAlerts", sendAlert);
             

                insert = cmd.ExecuteNonQuery();
                Credentials.writeLogFile("insert1" + insert);
                /**
                 * 
                 * RECORRER LISTA DE FTP
                 * 
                 * */
               
                foreach (Dtl.DistribuitorPhase listaFase in lisDistPhase)
                {
                    if (listaFase.typeOperation == true)//GUARDAR FASE
                    {
                        cmd.CommandText = "dbo.dms_Set_DistributorPhase";
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@IdDistributor", listaFase.IdDistributor);
                        cmd.Parameters.AddWithValue("@IdPhase", listaFase.IdPhase);
                        cmd.Parameters.AddWithValue("@dsStartDate", listaFase.dsStartDate);
                        cmd.Parameters.AddWithValue("@dsEndDate", listaFase.dsEndDate);
                        cmd.Parameters.AddWithValue("@nmFTP", listaFase.nmFTP);
                        cmd.Parameters.AddWithValue("@nmUserFTP", listaFase.nmUserFTP);
                        cmd.Parameters.AddWithValue("@nmPassFTP", listaFase.nmPassFTP);
                        cmd.Parameters.AddWithValue("@nmFTPFolder", listaFase.nmFTPFolder);
                        cmd.Parameters.AddWithValue("@idStatus", listaFase.idStatus);
                        
                    }
                    else//MODIFICAR FASE
                    {
                        
                        cmd.CommandText = "dbo.dms_Set_DistributorPhase_change";
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@IdDistributorPhase", listaFase.IdDistributorPhase);                         
                        cmd.Parameters.AddWithValue("@IdPhase", listaFase.IdPhase);
                        cmd.Parameters.AddWithValue("@dsStartDate", listaFase.dsStartDate);
                        cmd.Parameters.AddWithValue("@dsEndDate", listaFase.dsEndDate);
                        cmd.Parameters.AddWithValue("@nmFTP", listaFase.nmFTP);
                        cmd.Parameters.AddWithValue("@nmUserFTP", listaFase.nmUserFTP);
                        cmd.Parameters.AddWithValue("@nmPassFTP", listaFase.nmPassFTP);
                        cmd.Parameters.AddWithValue("@nmFTPFolder", listaFase.nmFTPFolder);
                        cmd.Parameters.AddWithValue("@idStatus", listaFase.idStatus);
                         
                    }
                    
                    insert = cmd.ExecuteNonQuery();
                    
                  
                }


                /**
                 * 
                 * RECORRER LISTA DE EMAIL
                 * 
                 * */
               
                foreach (Dtl.DistribuitorEmail listaEmail in listEmail)
                {
                    if (listaEmail.typeOperation == true)//GUARDAR EMAIL
                    {
                        cmd.CommandText = "dbo.dms_Set_DistributorEmail";
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@IdDistributor", listaEmail.idDistributor);
                        cmd.Parameters.AddWithValue("@DsDistributorEmail", listaEmail.dsDistributorEmail);
                        cmd.Parameters.AddWithValue("@Active", listaEmail.active);
                    }
                    else//MODIFICAR EMAIL
                    {
                        cmd.CommandText = "dbo.dms_Set_DistributorEmail_Change";
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@IdDistributorEmail", listaEmail.idDistributorEmail);
                        cmd.Parameters.AddWithValue("@IdDistributor", listaEmail.idDistributor);
                        cmd.Parameters.AddWithValue("@DsDistributorEmail", listaEmail.dsDistributorEmail);
                        cmd.Parameters.AddWithValue("@Active", listaEmail.active);

                    }
                    insert = cmd.ExecuteNonQuery();
                    
                   

                }

                tr.Commit();
                cmd.Connection.Close();                 
                return insert;
            }
            catch (Exception exc)
            {
                tr.Rollback();
                cmd.Connection.Close();
                Credentials.writeLogFile("setDistribuitorPhaseAndEmail+: " + exc.Message);
                return 0;
            }


           
        }

        public static int DeleteEmail(int idDistribuitorEmail) 
        {
            int delete = 0;
            string connection = Credentials.getCredentias("DMSTools");
            if (connection == null)
            {
                return 0;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_Delete_DistributorEmail";
            cmd.Parameters.AddWithValue("@idDistributorEmail", idDistribuitorEmail);
            

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                delete = 1;
                return delete;
            }
            catch (SqlException exc)
            {

                Credentials.writeLogFile("DeleteEmail: " + exc.Message);
                return 0;
            }
           
             
        }

        public static int DeletePhase(int idDistrbuitorPhase)
        {
            int delete = 0;
            string connection = Credentials.getCredentias("DMSTools");
            if (connection == null)
            {
                return 0;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_delete_DistributorPhase";
            cmd.Parameters.AddWithValue("@IdDistributorPhase", idDistrbuitorPhase);


            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                delete = 1;
                return delete;
            }
            catch (SqlException exc)
            {

                Credentials.writeLogFile("DeletePhase: " + exc.Message);
                return 0;
            }


        }


    }
}
